﻿using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Collections.Generic;

namespace CDOP.Model
{
    public class Chart
    {
        private static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["ChartConnection"].ConnectionString;

        public static DataView GetRecordsByEthnicGroup()
        {
            const string query = "SELECT COUNT(*) as Count , Ethnic_Group as EthnicGroup FROM MasterTable GROUP BY Ethnic_Group";
            var adapter = new SqlDataAdapter(query, ConnectionString);
            var table = new DataTable();
            adapter.Fill(table);
            var view = table.DefaultView;
            return view;
        }

        public static DataView GetDynamicChartData(string columnName)
        {
            var query ="SELECT COUNT(*) as Count , " + columnName + " as " + columnName + " FROM MasterTable GROUP BY " +columnName;
            var adapter = new SqlDataAdapter(query, ConnectionString);
            var table = new DataTable();
            adapter.Fill(table);
            var view = table.DefaultView;
            return view;
        }

        public static List<ColumnData> GetColumnNamesForDynamicChart()
        {
            const string query = "SELECT Name FROM sys.columns WHERE object_id = OBJECT_ID('dbo.MasterTable')";
            var adapter = new SqlDataAdapter(query, ConnectionString);
            var table = new DataTable();
            adapter.Fill(table);
            var names = (from DataRow dr in table.Rows select new ColumnData {Name = dr["Name"].ToString()}).ToList();
            return names;
        }


        public static DataView GetDeathsByGenderForLocalAuthority(string localAuthority)
        {
            var connection = new SqlConnection(ConnectionString);

            //specify the sp name
            var cmd = new SqlCommand("GetDeathsByGenderForLocalAuthority",connection) {CommandType = CommandType.StoredProcedure};
            //bind parameters for the sp
            var param = new SqlParameter("@LocalAuthority", localAuthority);
            cmd.Parameters.Add(param);

            var dap = new SqlDataAdapter(cmd);
            var ds = new DataSet();
            if (connection.State == ConnectionState.Closed)
                connection.Open();
            dap.Fill(ds);
            if (connection.State == ConnectionState.Open)
                connection.Close();

            return ds.Tables[0].DefaultView;
        }


        public static DataView GetCasesReviewed()
        {
            var connection = new SqlConnection(ConnectionString);
            var cmd = new SqlCommand("sp_Reports_Cases_Reviewed", connection) { CommandType = CommandType.StoredProcedure };
            var dap = new SqlDataAdapter(cmd);
            var ds = new DataSet();
            if (connection.State == ConnectionState.Closed)
                connection.Open();
            dap.Fill(ds);
            if (connection.State == ConnectionState.Open)
                connection.Close();
            return ds.Tables[0].DefaultView;

        }

        public static DataView GetCategoriesOfDeath()
        {
            var connection = new SqlConnection(ConnectionString);
            var cmd = new SqlCommand("sp_Reports_Categories_Of_Death", connection) { CommandType = CommandType.StoredProcedure };
            var dap = new SqlDataAdapter(cmd);
            var ds = new DataSet();
            if (connection.State == ConnectionState.Closed)
                connection.Open();
            dap.Fill(ds);
            if (connection.State == ConnectionState.Open)
                connection.Close();
            return ds.Tables[0].DefaultView;

        }

        public static DataView GetDeathByGender()
        {
            var connection = new SqlConnection(ConnectionString);
            var cmd = new SqlCommand("sp_Reports_Death_By_Gender", connection) { CommandType = CommandType.StoredProcedure };
            var dap = new SqlDataAdapter(cmd);
            var ds = new DataSet();
            if (connection.State == ConnectionState.Closed)
                connection.Open();
            dap.Fill(ds);
            if (connection.State == ConnectionState.Open)
                connection.Close();
            return ds.Tables[0].DefaultView;

        }



    }

    public class ColumnData
    {
        public string Name { get; set; }
    }
    
}